const express = require("express");
const router = express.Router();
const path = require('path');
const fs = require('fs');
const pool = require("../pool.js");
const utils = require("../utils/index.js");
const moment = require("moment-timezone");
const multer = require("multer");
const axios = require("axios");
const CryptoJS = require("crypto-js");
const FormData = require("form-data");

const config = require("../config");
const { content } = require("googleapis/build/src/apis/content/index.js");
const apiKey = 'FdXdzDJbfkPJWRw2R2fLNgb6cnG201RS';

const util = require('util'); // 引入 util 用于 promisify
const exec = util.promisify(require('child_process').exec); // 将exec函数 Promise化



// 配置 multer 的存储设置
const storage = multer.diskStorage({
    destination: function (req, file, cb) {
        // 设置文件上传目录
        cb(null, "/home/wwwroot/default/aiNoteMedia");
    },
    filename: function (req, file, cb) {
        // 生成文件名
        cb(null, Date.now() + "-" + file.originalname);
    },
});

const upload = multer({ storage: storage });

// 创建文件上传的 POST 接口
router.post("/upload", upload.single("file"), (req, res) => {
    console.log('进上传接口')
    const file = req.file;
    const { pk } = req.body;
    if (!file) {
        return res.status(400).send("请上传一个文件。");
    }
    let fileUrl = ''

    fileUrl = `https://pdf.seamobitech.com/aiNoteMedia/${file.filename}`;



    // 返回文件存储地址
    res.send({
        code: 0,
        message: "success",
        fileUrl: fileUrl,
    });
});
// 删除文件接口
router.post("/deleteFile", (req, res) => {
    // 建议用 POST，防止恶意随机拼接口；参数用 req.body
    const { filename } = req.body;
    console.log(filename)
    if (!filename) {
        return res.status(400).json({ code: 1, message: '缺少文件名参数！' });
    }

    // 检查文件名是否有目录穿越，防止安全漏洞
    if (filename.includes('/') || filename.includes('\\')) {
        return res.status(400).json({ code: 2, message: '非法文件名！' });
    }

    const dir = "/home/wwwroot/default/aiNoteMedia";
    const filePath = path.join(dir, filename);

    fs.unlink(filePath, (err) => {
        if (err) {
            // 文件不存在会出错等
            if (err.code === 'ENOENT') {
                return res.status(404).json({ code: 3, message: '文件不存在！' });
            }
            return res.status(500).json({ code: 500, message: '删除失败', error: err.message });
        }
        res.json({ code: 0, message: '删除成功！' });
    });
});
// 加密函数
function encrypt(message, key, iv) {
    const keyHex = CryptoJS.enc.Utf8.parse(key);
    const ivHex = CryptoJS.enc.Utf8.parse(iv);
    // 加密模式为CBC，填充模式为PKCS7
    const encrypted = CryptoJS.DES.encrypt(message, keyHex, {
        iv: ivHex,
        mode: CryptoJS.mode.CBC,
        padding: CryptoJS.pad.Pkcs7,
    });
    return encrypted.toString();
}

// 解密函数
function decrypt(ciphertext, key, iv) {
    const keyHex = CryptoJS.enc.Utf8.parse(key);
    const ivHex = CryptoJS.enc.Utf8.parse(iv); // 使用key的前8个字符作为IV
    // 解密模式为CBC，填充模式为PKCS7
    const decrypted = CryptoJS.DES.decrypt(
        {
            ciphertext: CryptoJS.enc.Base64.parse(ciphertext),
        },
        keyHex,
        {
            iv: ivHex,
            mode: CryptoJS.mode.CBC,
            padding: CryptoJS.pad.Pkcs7,
        }
    );
    return decrypted.toString(CryptoJS.enc.Utf8);
}
router.post('/getTokens', async (req, res) => {
    const { dateRange } = req.body;
    let sql = `SELECT * FROM ainote_token_usage_per_day WHERE 1=1`;
    let params = [];

    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sql += ` AND date BETWEEN ? AND ?`;
        params.push(startDate, endDate);
    }

    try {
        const results = await mysqlQuery(sql, params);

        const aggregatedData = {};

        results.forEach(row => {
            const {
                date, prompt_tokens, completion_tokens, total_tokens,
                prompt_dollar, completion_dollar, total_dollar, model, update_date
            } = row;

            if (!aggregatedData[date]) {
                aggregatedData[date] = {
                    date,
                    prompt_tokens: 0,
                    completion_tokens: 0,
                    total_tokens: 0,
                    prompt_dollar: 0,
                    completion_dollar: 0,
                    total_dollar: 0,
                    update_date: update_date,
                    model: model,
                };
            }

            aggregatedData[date].prompt_tokens += prompt_tokens;
            aggregatedData[date].completion_tokens += completion_tokens;
            aggregatedData[date].total_tokens += total_tokens;

            // 使用四舍五入并转换为数字
            aggregatedData[date].prompt_dollar = parseFloat((aggregatedData[date].prompt_dollar + prompt_dollar).toFixed(4));
            aggregatedData[date].completion_dollar = parseFloat((aggregatedData[date].completion_dollar + completion_dollar).toFixed(4));
            aggregatedData[date].total_dollar = parseFloat((aggregatedData[date].total_dollar + total_dollar).toFixed(4));
        });

        const aggregatedArray = Object.values(aggregatedData);
        const convertedResults = aggregatedArray.map(result => {
            const convertedResult = { ...result };

            if (convertedResult.date) {
                convertedResult.date = convertToChinaTime(convertedResult.date);
            }

            if (convertedResult.update_date) {
                convertedResult.update_date = convertToChinaTime(convertedResult.update_date);
            }

            return convertedResult;
        });

        res.send(
            utils.returnData({
                data: convertedResults
            })
        );
    } catch (error) {
        console.error('Error fetching token usage data:', error);
        res.status(500).send('Internal Server Error');
    }
});
// 时间转换函数，只保留 "月-日" 部分
function convertToChinaTime(utcDateStr) {
    const utcDate = new Date(utcDateStr);
    const chinaDate = new Date(utcDate.getTime() + 8 * 60 * 60 * 1000); // 添加8小时
    const year = chinaDate.getUTCFullYear(); // 提取年份，后续比较
    const month = String(chinaDate.getUTCMonth() + 1).padStart(2, '0'); // 月份从0开始，需要加1
    const day = String(chinaDate.getUTCDate()).padStart(2, '0'); // 日期
    return `${month}-${day}`;
}
router.post('/whisper', upload.single('file'), async (req, res) => {

    try {
        if (!req.file) {
            return res.status(400).json({ error: '未提供音频文件' });
        }
        const formData = new FormData();
        formData.append('file', fs.createReadStream(req.file.path), req.file.originalname);

        formData.append('response_format', 'verbose_json');
        formData.append('speaker_labels', 'true');
        ['word'].forEach(granularity => {
            formData.append('timestamp_granularities[]', granularity);
        });
        const apiResponse = await axios.post('https://api.lemonfox.ai/v1/audio/transcriptions', formData, {
            headers: {
                'Authorization': `Bearer 0FvTZB6WauyvhMlWaoUMPxc6OutjnivP`
            },
            timeout: 6000000
        });
        // 删除临时上传的文件
        fs.unlink(req.file.path, (err) => {
            if (err) {
                console.error('删除临时文件时出错：', err);
            }
        });
        res.send({
            code: 0,
            msg: 'success',
            data: apiResponse.data
        })
    } catch (error) {
        console.error(error);
        // 清理上传的临时文件（如存在）
        if (req.file && req.file.path) {
            fs.unlink(req.file.path, () => { });
        }
        res.status(500).json({ error: error.message || '服务器内部错误' });
    }
});

router.post("/getAinoteConfig", async (req, res) => {
    const { version } = req.body
    let sql = `SELECT * FROM ainote_config WHERE version = ?`;
    let result = await mysqlQuery(sql, [version]);
    res.send({ code: 0, data: result[0] })
});
router.post("/updateAinoteConfig", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE ainote_config
            SET 
                prompt_test = ?, 
                temperature_test = ?, 
                max_tokens_test = ?, 
                top_p_test = ?, 
                frequency_penalty_test = ?, 
                presence_penalty_test = ?, 
                model_test = ?, 
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post("/updateAinoteConfigHandel", async (req, res) => {
    let { name } = await utils.getUserInfo(req, res);
    const { prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, id } = req.body
    const sql = `
            UPDATE ainote_config
            SET 
                prompt_release = ?,
                temperature = ?,
                max_tokens = ?,
                top_p = ?,
                frequency_penalty = ?,
                presence_penalty = ?,
                model = ?,
                update_by = ?,
                update_date = ?
            WHERE id = ?
        `;
    let result = await mysqlQuery(sql, [prompt_test, temperature_test, max_tokens_test, top_p_test, frequency_penalty_test, presence_penalty_test, model_test, name, utils.formatDate(new Date()), id])
    res.send({ code: 0, data: result[0] })
});
router.post('/getAinoteVersionList', async (req, res) => {
    try {
        let sql = `SELECT version FROM ainote_config`;
        let result = await mysqlQuery(sql, []);
        const formattedResult = result.map(row => ({
            id: row.version,
            name: row.version
        }));

        res.send({
            data: formattedResult
        });
    } catch (error) {
        console.error("Error fetching versions:", error);
        res.status(500).json({ error: "Internal server error" });
    }
});
router.post('/addAinoteVersion', async (req, res) => {
    const { version } = req.body;
    try {
        // 检查表中是否已经有相同的版本
        const existingVersion = await mysqlQuery('SELECT 1 FROM ainote_config WHERE version = ?', [version]);
        if (existingVersion.length > 0) {
            return res.send({ code: 1, msg: '版本已存在' });
        }
        // 获取表中最后一条记录
        const lastRecord = await mysqlQuery('SELECT * FROM ainote_config ORDER BY id DESC LIMIT 1', []);

        if (lastRecord.length === 0) {
            return res.send({ code: 1, msg: '没有可复制的记录' });
        }
        // 从最后一条记录中获取值，并修改版本
        let newRecord = { ...lastRecord[0], version };

        delete newRecord.id;
        newRecord.id = utils.createId();
        // 插入新记录
        const insertQuery = 'INSERT INTO ainote_config SET ?';
        await mysqlQuery(insertQuery, [newRecord]);

        res.send({ code: 0, msg: 'success' })

    } catch (error) {
        console.error(error);
        res.status(500).json({ error: '数据库操作失败，请稍后重试' });
    }
})

router.post("/textRefine", async (req, res) => {
    let parseData = req.body.query;
    let content = JSON.parse(decrypt(parseData, config.cryptoKey, config.cryptoIv));
    if (content.code != 'OZqcJQLBUyevTh') return
    const { textContent, version, pk, language } = content
    console.log(textContent,'textContent')
    console.log(version,'version')
    console.log(pk,'pk')
    let sql = `SELECT * FROM ainote_config WHERE version = ?`;
    let params = [version || '1.0.0'];
    let result = await mysqlQuery(sql, params);
    if (result.length === 0 && version) {
        sql = `
         SELECT * FROM ainote_config
        ORDER BY id DESC
        LIMIT 1
    `;
        result = await mysqlQuery(sql);
    }
    let { prompt_release, temperature, max_tokens, top_p, frequency_penalty, presence_penalty, model } = result[0]

    // 替换 prompt
    let processedPrompt = prompt_release || '';
    if (language) processedPrompt = processedPrompt.replace(/\{language\}/g, language);

    let queryContent;
    queryContent = [
        {
            type: 'text',
            text: textContent
        }

    ];
    try {
        const response = await axios.post(
            'https://api.openai.com/v1/chat/completions',
            {
                model: model,
                messages: [
                    {
                        role: 'system',
                        content: [
                            {
                                type: 'text',
                                text: processedPrompt
                            }
                        ]
                    },
                    {
                        role: 'user',
                        content: queryContent
                    }
                ],
                response_format: {
                    type: 'text'
                },
                temperature: Number(temperature),
                max_tokens: Number(max_tokens),
                top_p: Number(top_p),
                frequency_penalty: Number(frequency_penalty),
                presence_penalty: Number(presence_penalty)
            },
            {
                headers: {
                    'Content-Type': 'application/json',
                    'Authorization': `Bearer   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`
                }
            }
        );
        // 获取 token 消耗信息
        const { usage } = response.data;
        const { prompt_tokens, completion_tokens, total_tokens } = usage;
        // 存储 token 消耗到数据库
        const date = utils.formatDate1(new Date());
        let id = utils.createId();
        // 需要插入的内容
        let message = null;

        if (response.data.choices[0].message) {
            message = JSON.stringify(response.data.choices[0].message);
        }
        console.log('ainode----message', message)
        const messageData = JSON.parse(message);
        console.log('ainode----messageData', messageData)
        const parsedData = extractAndParseJson(messageData.content);
        console.log('ainode----parsedData', parsedData)
        let title = null
        if (parsedData?.title) {
            title = parsedData.title
        }
        let summary = null
        if (parsedData?.summary) {
            summary = parsedData.summary
        }
        let code = '0'
        if (!title || !summary) {
            code = '1'
            summary = JSON.stringify(response.data.choices[0].message)
        }
        const insertSql = `INSERT INTO ainote_log (id,pk,code,version,date, prompt_tokens, completion_tokens, total_tokens,title,summary,model,create_date,update_date) VALUES (?,?,?, ?,?, ?,?,?, ?,?,?, ?,?)`;
        await mysqlQuery(insertSql, [id, pk, code, version, date, prompt_tokens, completion_tokens, total_tokens, title, summary, model, utils.formatDate(new Date()), utils.formatDate(new Date())]);
        let data = encrypt(
            JSON.stringify(response.data.choices[0].message),
            config.cryptoKey,
            config.cryptoIv
        );
        res.send({
            code: 0,
            msg: 'success',
            data: data
        })
    } catch (error) {
        console.error('Error making API request:', error.response ? error.response.data : error.message);
        res.send({
            code: 1,
            msg: error.response ? error.response.data : error.message,
        })
    }

});
function extractAndParseJson(content) {
    try {
        // 1. 提取 ```json ... ``` 或 ``` ... ```
        let match = content.match(/```json\s*([\s\S]+?)\s*```/) ||
            content.match(/```\s*([\s\S]+?)\s*```/);
        let jsonStr = match ? match[1] : content;

        // 2. 直接JSON.parse即可！
        return JSON.parse(jsonStr);
    } catch (error) {
        console.error('JSON解析失败:', error.message);
        console.debug('原始内容:', content);
        return null;
    }
}
// function extractAndParseJson(content) {
//     try {
//         // 阶段1：提取核心JSON字符串
//         let jsonStr = content;

//         // 处理带代码块的情况
//         if (content.includes('```json')) {
//             jsonStr = content.split('```json')[1].split('```')[0].trim();
//         }
//         // 处理带其他代码块的情况
//         else if (content.startsWith('```')) {
//             jsonStr = content.replace(/^```/gm, '').trim();
//         }

//         // 阶段2：清洗特殊字符
//         const sanitized = jsonStr
//             .replace(/\\n/g, '')     // 移除转义换行
//             .replace(/\\"/g, '"')   // 转换转义引号
//             .replace(/[\u0000-\u001F]/g, ''); // 移除控制字符

//         // 阶段3：尝试解析
//         return JSON.parse(sanitized);
//     } catch (error) {
//         console.error('JSON解析失败:', error.message);
//         console.debug('原始内容:', content);
//         return null;
//     }
// }
router.post("/getApiLog", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { type, dateRange, pk, code } = req.body;
    let sqlConditions = [];
    const sqlParams = [];
    if (code) {
        sqlConditions.push("code = ?");
        sqlParams.push(code);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sqlConditions.push("update_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }
    // 根据 pk 参数添加条件
    if (pk) {
        if (pk == "com.zhijian.AINoteTaker") {
            sqlConditions.push("pk = ?");
            sqlParams.push(pk);
        } else if (pk == "com.finger.AINoteTaker") {
            sqlConditions.push("(pk = ? OR pk IS NULL OR pk = '')");
            sqlParams.push(pk);
        }
    }
    sqlConditions.push("state = 0");
    // 添加条件：确保 message 字段有值

    let sqlBase = `SELECT * FROM ainote_log`;
    let totalBase = "FROM ainote_log";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY update_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);
    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {

            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");

        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});
router.post("/deleteHandel", async (req, res) => {
    let { id } = req.body;
    const sql = `UPDATE ainote_api_log SET state = 1 WHERE id = ?`;
    let result = await mysqlQuery(sql, [id])

    res.send(
        {
            code: 0,
            msg: 'success'
        }
    );
});

router.post("/batchDeleteHandel", async (req, res) => {
    const items = req.body.items;
    for (const item of items) {
        const { id } = item;
        const sql = `UPDATE ainote_api_log SET state = 1 WHERE id = ?`;
        await mysqlQuery(sql, [id]);
    }
    res.send({
        code: 0,
        msg: 'success'
    });
});

router.post("/getUserList", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { uuid, dateRange } = req.body;
    let sqlConditions = [];

    const sqlParams = [];
    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }
    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');

        sqlConditions.push("update_date BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }


    let sqlBase = `SELECT * FROM ainote_user_list`;
    let totalBase = "FROM ainote_user_list";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY update_date DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);
    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {

            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");

        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});
router.post("/getChatList", async (req, res) => {
    const { uuid } = req.body;

    const sqlParams = [];
    let sqlConditions = []; // 初始化SQL条件数组

    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }

    let sqlBase = `SELECT * FROM ainote_aichat_list`;
    let totalBase = "FROM ainote_aichat_list";

    // 如果有额外的条件，添加到基础SQL中
    if (sqlConditions.length > 0) {
        const whereClause = sqlConditions.join(' AND ');
        sqlBase += ` WHERE ${whereClause}`;
        totalBase += ` WHERE ${whereClause}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    //获取总数
    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    // 排序但不分页
    sqlBase += " ORDER BY eventTimeMillis ASC";

    // 执行查询
    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {
            element.update_date = moment(element.update_date)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});
router.post('/storeChatMessages', async (req, res) => {
    const { uuid, content, role, eventTimeMillis } = req.body;
    let contents = {};
    contents.id = utils.createId();
    contents.uuid = uuid;
    contents.content = content;
    contents.role = role;
    contents.eventTimeMillis = eventTimeMillis;
    contents.update_date = utils.formatDate(new Date());

    try {
        // 检查uuid是否存在于ainote_user_list表中
        const checkSql = 'SELECT COUNT(*) AS count FROM ainote_user_list WHERE uuid = ?';
        const checkResult = await mysqlQuery(checkSql, [uuid]);

        if (checkResult[0].count === 0) {
            // 如果没有重复，插入新的uuid
            const insertUserSql = 'INSERT INTO ainote_user_list (id, uuid) VALUES (?, ?)';
            await mysqlQuery(insertUserSql, [utils.createId(), uuid]);
        } else {
            // 如果有重复，更新现有记录的update_date
            const updateUserSql = 'UPDATE ainote_user_list SET update_date = ? WHERE uuid = ?';
            await mysqlQuery(updateUserSql, [utils.formatDate(new Date()), uuid]);
        }

        // 插入聊天记录到ainote_user_list
        const insertChatSql = `
            INSERT INTO ainote_user_list (id, uuid, content, role, eventTimeMillis, update_date)
            VALUES (?, ?, ?, ?, ?,?)
        `;
        const insertChatResult = await mysqlQuery(insertChatSql, [utils.createId(), uuid, content, role, eventTimeMillis, utils.formatDate(new Date())]);

        if (insertChatResult.affectedRows === 1) {
            res.send({ code: 0, msg: "success" });
        } else {
            res.status(500).send({ code: 1, msg: "Failed to insert chat message" });
        }
    } catch (error) {
        console.error('Database Error:', error);
        res.status(500).send({ code: 1, msg: "Database error" });
    }
});


router.post('/apilog', async (req, res) => {
    // 1. 获取body参数
    const {
        uuid, pk, audio_url,
        whisper_result, llm_note, error_type,
        llm_title, api_type, llm_error_info, length,
        audio_lang, note_lang, status
    } = req.body;

    // 必传校验
    if (!uuid || !pk || !audio_url) {
        return res.status(400).json({ code: 400, message: 'uuid, pk, audio_url 为必填！' });
    }

    try {
        const id = utils.createId();
        // 处理 length：如果有值，向上取整成"分钟"
        let lengthMinute = null;
        if (length !== undefined && length !== null && length !== '') {
            // 转数字且秒->分钟向上取整，不足一分钟算1
            const seconds = Number(length);
            if (!isNaN(seconds)) {
                lengthMinute = Math.ceil(seconds / 60);
                if (lengthMinute < 1) lengthMinute = 1;
            }
        }
        // 字段顺序和SQL一一对应
        const insertSql = `
            INSERT INTO ainote_api_log (
                id, uuid, pk, audio_url,
                whisper_result, llm_note, error_type,
                llm_title, api_type, llm_error_info, length,
                audio_lang, note_lang, status,
                created_at, updated_at
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?, NOW(), NOW())
        `;

        const params = [
            id,
            uuid,
            pk,
            audio_url,
            whisper_result || null,
            llm_note || null,
            error_type || null,
            llm_title || null,
            api_type || null,
            llm_error_info || null,
            lengthMinute || null,
            audio_lang || null,
            note_lang || null,
            status || null
        ];

        await mysqlQuery(insertSql, params);

        res.json({ code: 0, message: 'success' });
    } catch (e) {
        console.error('apilog上报失败', e);
        res.status(500).json({ code: 500, message: '数据库上报失败！' });
    }
});

router.post("/getApiLogNew", async (req, res) => {
    let { page, size } = utils.pageSize(req.body.page, req.body.size);
    let { dateRange, pk, error_type, uuid, api_type, audio_lang, note_lang, status, length } = req.body;
    let sqlConditions = [];
    const sqlParams = [];

    // 字符等值筛选
    if (error_type) {
        sqlConditions.push("error_type = ?");
        sqlParams.push(error_type);
    }
    if (uuid) {
        sqlConditions.push("uuid = ?");
        sqlParams.push(uuid);
    }
    if (api_type) {
        sqlConditions.push("api_type = ?");
        sqlParams.push(api_type);
    }
    if (audio_lang) {
        sqlConditions.push("audio_lang = ?");
        sqlParams.push(audio_lang);
    }
    if (note_lang) {
        sqlConditions.push("note_lang = ?");
        sqlParams.push(note_lang);
    }
    if (status !== undefined && status !== null && status !== '') {
        sqlConditions.push("status = ?");
        sqlParams.push(status);
    }
    // 数值区间筛选 (length 形如: [min, max])
    if (length && Array.isArray(length) && length.length === 2) {
        let [min, max] = length;
        // 只要不是都为0才加where条件
        if (
            min !== undefined && min !== null && min !== '' &&
            max !== undefined && max !== null && max !== '' &&
            !(min == 0 && max == 0) // 新增：都为0时不查
        ) {
            sqlConditions.push("length BETWEEN ? AND ?");
            sqlParams.push(min, max);
        }
    }

    if (dateRange && dateRange.start && dateRange.end) {
        const startDate = moment(dateRange.start).startOf('day').format('YYYY-MM-DD HH:mm:ss');
        const endDate = moment(dateRange.end).endOf('day').format('YYYY-MM-DD HH:mm:ss');
        sqlConditions.push("updated_at BETWEEN ? AND ?");
        sqlParams.push(startDate, endDate);
    }

    // 根据 pk 参数添加条件
    if (pk) {
        if (pk == "com.zhijian.AINoteTaker") {
            sqlConditions.push("pk = ?");
            sqlParams.push(pk);
        } else if (pk == "com.finger.AINoteTaker") {
            sqlConditions.push("(pk = ? OR pk IS NULL OR pk = '')");
            sqlParams.push(pk);
        }
    }

    sqlConditions.push("state = 0");

    let sqlBase = `SELECT * FROM ainote_api_log`;
    let totalBase = "FROM ainote_api_log";

    if (sqlConditions.length > 0) {
        sqlBase += ` WHERE ${sqlConditions.join(" AND ")}`;
        totalBase += ` WHERE ${sqlConditions.join(" AND ")}`;
    }

    let sqlCount = `SELECT COUNT(*) AS total_count ${totalBase}`;

    let { total } = await utils.getListSum(sqlCount, sqlParams, res, req);

    sqlBase += " ORDER BY updated_at DESC LIMIT ? OFFSET ?";
    sqlParams.push(size, page);

    await pool.query(sqlBase, sqlParams, (err, result) => {
        if (err) return res.send(utils.returnData({ code: -1, err, req }));
        result.forEach((element) => {
            element.updated_at = moment(element.updated_at)
                .tz("Asia/Shanghai")
                .format("YYYY-MM-DD HH:mm:ss");
        });
        res.send(
            utils.returnData({
                data: result,
                total: total,
            })
        );
    });
});
router.post('/getApiLogStatusList', async (req, res) => {
    try {
        // 去重拿status，不要null（不等于null筛掉）
        const sql = 'SELECT DISTINCT status FROM ainote_api_log WHERE status IS NOT NULL ORDER BY status ASC';
        const result = await mysqlQuery(sql, []);

        const statusList = result.map(row => ({
            id: row.status,
            name: String(row.status) // 前端自定义展示映射，如果要后端映射可以补充
        }));

        res.json({
            code: 0,
            msg: 'success',
            data: statusList
        });
    } catch (e) {
        res.status(500).json({ code: 500, msg: '数据库异常', error: e.message });
    }
});
// 获取去重的audio_lang列表
router.post('/getApiLogAudioLangList', async (req, res) => {
    try {
        const sql = 'SELECT DISTINCT audio_lang FROM ainote_api_log WHERE audio_lang IS NOT NULL AND audio_lang != \'\' ORDER BY audio_lang ASC';
        const result = await mysqlQuery(sql, []);
        const audioLangList = result.map(row => ({
            id: row.audio_lang,
            name: String(row.audio_lang)
        }));
        res.json({
            code: 0,
            msg: 'success',
            data: audioLangList
        });
    } catch (e) {
        res.status(500).json({ code: 500, msg: '数据库异常', error: e.message });
    }
});
// 获取去重的note_lang列表
router.post('/getApiLogNoteLangList', async (req, res) => {
    try {
        const sql = 'SELECT DISTINCT note_lang FROM ainote_api_log WHERE note_lang IS NOT NULL AND note_lang != \'\' ORDER BY note_lang ASC';
        const result = await mysqlQuery(sql, []);
        const noteLangList = result.map(row => ({
            id: row.note_lang,
            name: String(row.note_lang)
        }));
        res.json({
            code: 0,
            msg: 'success',
            data: noteLangList
        });
    } catch (e) {
        res.status(500).json({ code: 500, msg: '数据库异常', error: e.message });
    }
});
const cookieFiles = [
    '/home/wwwroot/default/aiNoteMedia/cookies1.txt',
    '/home/wwwroot/default/aiNoteMedia/cookies2.txt',
    '/home/wwwroot/default/aiNoteMedia/cookies3.txt',
    '/home/wwwroot/default/aiNoteMedia/cookies4.txt',
    '/home/wwwroot/default/aiNoteMedia/cookies5.txt',
    '/home/wwwroot/default/aiNoteMedia/cookies6.txt',
    '/home/wwwroot/default/aiNoteMedia/cookies7.txt',
];
function pickCookie() {
    // 每次随机选一份
    const idx = Math.floor(Math.random() * cookieFiles.length);
    return cookieFiles[idx];
}
// router.post('/getMediaUrl', async (req, res) => {
//     const { youtubeUrl } = req.body;
//     if (!youtubeUrl) {
//         return res.status(400).json({ success: false, message: '缺少 youtubeUrl 参数' });
//     }

//     const cookiesPath = pickCookie();
//     const userAgent = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/122.0.0.0 Safari/537.36';
//     const command = `yt-dlp --user-agent "${userAgent}" --cookies "${cookiesPath}" -f best --get-url "${youtubeUrl}"`;

//     try {
//         const { stdout, stderr } = await exec(command);
//         if (stderr) {
//             console.error('yt-dlp 命令行警告/错误:', stderr);
//         }
//         const mediaUrl = stdout.trim();
//         return res.json({
//             success: true,
//             url: mediaUrl
//         });
//     } catch (err) {
//         console.error('调用yt-dlp异常', err);
//         return res.status(500).json({
//             success: false,
//             message: 'yt-dlp 执行出错，详情见服务器日志。'
//         });
//     }
// });


router.post('/link-to-summary', async (req, res) => {
    // 从请求体中获取 youtubeUrl 和 guid 参数
    const { youtubeUrl, guid } = req.body;

    // 1. 检查youtubeUrl参数是否存在
    if (!youtubeUrl) {
        return res.status(400).json({ success: false, message: '缺少 youtubeUrl 参数' });
    }

    let videoId = ''; // 最终要提取的视频ID

    try {
        // 将youtubeUrl解析为URL对象，如果格式无效会抛出异常
        const youtubeUrlObj = new URL(youtubeUrl);

        // ------ 标准YouTube视频链接 ------
        // 比如 https://www.youtube.com/watch?v=xxxxxx
        if (youtubeUrlObj.hostname.includes('youtube.com')) {
            // 1. 标准 watch 地址，通过 searchParams 获取 v 参数
            videoId = youtubeUrlObj.searchParams.get('v');

            // 2. 直播模式, 比如https://www.youtube.com/live/xxxxxx?si=xxx
            // videoId 就在 pathname 的 /live/ 后面
            if (!videoId && youtubeUrlObj.pathname.startsWith('/live/')) {
                // pathname 格式: /live/xxxxxx
                // 用 split 分割并取最后一段
                videoId = youtubeUrlObj.pathname.split('/live/')[1];
            }
        }

        // ------ YouTube短地址 ------
        // 比如 https://youtu.be/xxxxxx
        if (youtubeUrlObj.hostname === 'youtu.be') {
            // 获取去掉第一个斜杠的部分作为videoId
            // pathname 示例: /xxxxxx
            videoId = youtubeUrlObj.pathname.replace('/', '');
        }

        // videoId 必须存在，否则认为提取失败
        if (!videoId) {
            return res.status(400).json({ success: false, message: '无法从URL中提取video_id参数' });
        }
    } catch (err) {
        // 捕获URL解析错误
        return res.status(400).json({ success: false, message: 'youtubeUrl格式无效' });
    }

    // 拼接要请求的Notegpt接口
    const apiUrl = `https://notegpt.io/api/v2/video-transcript?platform=youtube&video_id=${videoId}`;

    // 构造请求头
    const userAgent = 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/127.0.0.0 Safari/537.36';
    const defaultGuid = '97993b354f286bf5d52a76645086aae1';
    const cookieStr = `anonymous_user_id=${guid || defaultGuid};`;

    try {
        // 发送GET请求到notegpt接口
        const response = await axios.get(apiUrl, {
            headers: {
                'User-Agent': userAgent,
                'Cookie': cookieStr
            }
        });
        // 接口请求成功，返回数据
        return res.json({
            success: true,
            data: response.data
        });
    } catch (err) {
        // 接口请求失败，返回服务器错误
        console.error(err);
        return res.status(500).json({ success: false, message: '请求 NoteGPT 失败', error: err.message });
    }
});

async function mysqlQuery(sql, arr) {

    return new Promise((resolve, reject) => {
        pool.query(sql, arr, (error, results, fields) => {

            if (error) {
                console.log(error)
                return reject(error);
            }
            resolve(results);
        });
    });
}
module.exports = router;